# coding=utf-8
import datetime
import urllib

import pymysql
from lxml import etree

from bs4 import BeautifulSoup

db = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='',
    db='gupiao',
    charset='utf8'
)

# db = pymysql.Connect(
#     host='apiccr.megowx.com',
#     port=3306,
#     user='gupiao',
#     passwd='Aitrip123',
#     db='gupiao',
#     charset='utf8'
# )
cursor = db.cursor()

sql_dk = "INSERT INTO `fa_stock_dfk` (`date`, `code`, `jgcy`, `zlr`, `clr`, `zc1`, `zc20`, `kpflag`) VALUES "

lastTime = ""
try:
    sql0 = "SELECT date FROM `fa_stock_dfk` WHERE 1 ORDER BY id DESC LIMIT 1 "
    result = cursor.execute(sql0)
    db.commit()
    if result:
        date = cursor.fetchone()
        lastTime = datetime.datetime(year=date[0].year, month=date[0].month, day=date[0].day).strftime('%Y-%m-%d')
except Exception as e:
    print(e)


def getHtml(url):
    html = urllib.urlopen(url).read()
    html = html.decode('gbk')
    return html


def querydata(code):
    global lastTime
    code0 = code[3:]

    try:
        Url = "http://quote.eastmoney.com/sh600031.html"
        html = getHtml(Url)
        data = etree.HTML(html)

        zy1 = data.xpath('//*[@id="cwzbDataBox"]/tr[3]/td[2]')
        zy2 = data.xpath('//*[@id="cwzbDataBox"]/tr[3]/td[3]')[0].text
        zy3 = data.xpath('//*[@id="cwzbDataBox"]/tr[3]/td[4]')[0].text
        zy4 = data.xpath('//*[@id="cwzbDataBox"]/tr[3]/td[5]')[0].text
        zy5 = data.xpath('//*[@id="cwzbDataBox"]/tr[3]/td[6]')[0].text
        zy6 = data.xpath('//*[@id="cwzbDataBox"]/tr[3]/td[7]')[0].text
        zy7 = data.xpath('//*[@id="cwzbDataBox"]/tr[3]/td[8]')[0].text
        roe = data.xpath('//*[@id="cwzbDataBox"]/tr[1]/td[9]')[0].text

        # html = BeautifulSoup(html, 'lxml')
        # result = html.select('tr[class="tips-fieldnameL"]')
        # result2 = html.select('tr[class="tips-dataL"]')
        # result += result2
        # print(len(result))
        #
        # for site in result:
        #     item = {}
        #     name = site.select('td a')[0].get_text()
        #     detailLink = site.select('td a')[0].attrs['href']  # Tag就是 HTML 中的一个个标签，它的两个属性是name和attrs
        #     catalog = site.select('td ')[1].get_text()
        #     recruitNumber = site.select('td ')[2].get_text()
        #     workLocation = site.select('td ')[3].get_text()
        #     publishTime = site.select('td ')[4].get_text()

    except Exception as e:
        print(e)

    return True


code_list = []
try:
    sql = "SELECT code FROM `fa_stock_mk` where date = '2019-04-30' "
    result = cursor.execute(sql)

    if result:
        code_list = cursor.fetchall()

except Exception as e:
    print(e)

#### 打印结果集 ####
# sh.600000～sz.300773
num = 0
isFirst = 0

for i in range(0, len(code_list)):
    one = code_list[i]
    if isFirst == 0 and one[0] == "sh.600000":
        isFirst = 1

    if isFirst == 1:
        result = querydata(one[0])
        if result == 0:
            continue
        print('----Finish---' + str(num))
        num += 1
        if one[0] == "sz.300773":
            break
        if num > 0:
            break

cursor.close()
db.close()

print('----OK---')
